package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.DailyTask;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;
import java.util.Map;

@Mapper
public interface DailyTaskMapper_eight {

    @Select("SELECT " +
            "dt.task_id AS taskId, " +
            "dt.plan_id AS planId, " +
            "dt.user_id AS userId, " +
            "dt.task_date AS taskDate, " +
            "dt.status, " +
            "dt.created_at AS createdAt, " +
            "dt.updated_at AS updatedAt, " +
            "dt.content " +
            "FROM daily_tasks dt " +
            "JOIN users ui ON dt.user_id = ui.user_id " +
            "WHERE ui.username = #{username}")
    List<DailyTask> getTasksByUsername(@Param("username") String username);

    @Select("SELECT " +
            "task_id AS taskId, " +
            "plan_id AS planId, " +
            "user_id AS userId, " +
            "task_date AS taskDate, " +
            "status, " +
            "created_at AS createdAt, " +
            "updated_at AS updatedAt, " +
            "content " +
            "FROM daily_tasks")
    List<DailyTask> getListTasks();

    @Select("SELECT plan_id FROM study_plans WHERE user_id = #{userId} AND status != 'abandoned' ORDER BY created_at DESC LIMIT 1")
    Long getFirstPlanIdByUserId(@Param("userId") Long userId);

    @Update("UPDATE daily_tasks " +
            "SET status = #{status}, content = #{content}, updated_at = NOW() " +
            "WHERE task_id = #{taskId} AND user_id = #{userId}")
    int updateTaskStatus(@Param("taskId") Long taskId,
                         @Param("userId") Long userId,
                         @Param("status") String status,
                         @Param("content") String content);

    @Insert("INSERT INTO daily_tasks " +
            "(plan_id, user_id, task_date, status, created_at, updated_at, content) " +
            "VALUES (#{planId}, #{userId}, #{taskDate}, #{status, jdbcType=OTHER}, NOW(), NOW(), #{content})")
    @Options(useGeneratedKeys = true, keyProperty = "taskId", keyColumn = "task_id")
    @Results({
            @Result(property = "taskId", column = "task_id"),
            @Result(property = "planId", column = "plan_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "taskDate", column = "task_date"),
            @Result(property = "status", column = "status", jdbcType = JdbcType.OTHER),
            @Result(property = "content", column = "content")
    })
    int addDailyTask(DailyTask dailyTask);

    @Delete("DELETE FROM daily_tasks " +
            "WHERE task_id = #{taskId} AND user_id = #{userId}")
    int deleteDailyTask(@Param("taskId") Long taskId,
                        @Param("userId") Long userId);

    @Select("SELECT " +
            "dt.task_id AS taskId, " +
            "dt.plan_id AS planId, " +
            "dt.user_id AS userId, " +
            "dt.task_date AS taskDate, " +
            "dt.status, " +
            "dt.created_at AS createdAt, " +
            "dt.updated_at AS updatedAt, " +
            "dt.content " +
            "FROM daily_tasks dt " +
            "JOIN users ui ON dt.user_id = ui.user_id " +
            "WHERE ui.username = #{username} AND dt.task_date = #{date}")
    List<DailyTask> getTasksByUsernameAndDate(@Param("username") String username, @Param("date") String date);

    @Select("SELECT " +
            "COUNT(*) as total_tasks, " +
            "SUM(CASE WHEN dt.status = 'completed' THEN 1 ELSE 0 END) as completed_tasks " +
            "FROM daily_tasks dt " +
            "JOIN users ui ON dt.user_id = ui.user_id " +
            "WHERE ui.username = #{username}")
    Map<String, Object> getTaskStatisticsByUsername(@Param("username") String username);
}